package Zoo.Impl;

import Zoo.Entity.AnimalEntity;
import Zoo.Entity.ZoomenEntity1;
import Zoo.Service.ZoomenService1;
import Zoo.Util.TableDTO;
import lesson05.utils.JdbcUtils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * 1、动物饲养员登录功能
 * 2、查看动物饲养员的管理园区的动物的信息
 * 3、动物饲养员添加动物功能
 */

public class ZoomenService1Impl implements ZoomenService1 {
    //登录功能
    @Override
    public boolean validateZoomen(ZoomenEntity1 zoomenEntity1) {
        String zoomenName=zoomenEntity1.getZoomenName();
        String idNumber= zoomenEntity1.getIdNumeber();
        String sql ="SELECT `id_number` FROM `zoomen` WHERE `name`=?";
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        try {
            //获取数据库连接
            conn = JdbcUtils_DBCP.getConnection();
            if(conn==null){
                //如果没获取到数据库连接，则返回false
                return false;
            }
            st= conn.prepareStatement(sql);
            st.setString(1, zoomenEntity1.getZoomenName());
            rs = st.executeQuery();
            while(rs.next()){
                String pwd = rs.getString(1);
                if(zoomenEntity1.getIdNumeber().equals(pwd)){
                    //如果获取的身份证号是相对应的，则返回true
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(conn,st,rs);
        }




        //如果获取的用户名和身份证号是不匹配的，则返回false
        return false;
    }

    //查询功能
    @Override
    public TableDTO retrieveZoomen1(ZoomenEntity1 zoomenEntity1,String zoomenName1) {
        StringBuilder sql=new StringBuilder();
        sql.append("SELECT a.`id`,a.`name`,a.`kind`,a.`introduction`,`age`,`health`,`parkid` FROM `animals` AS a " +
                " LEFT JOIN `zooparks` AS zp " +
                " ON a.`parkid`=zp.`id`" +
                " LEFT JOIN  `zoomen` zm " +
                " ON zp.`adminid`=zm.`name` " +
                " WHERE zm.`name`= '"+zoomenName1+"'");
        if(zoomenEntity1.getSearchKey()!=null && !"".equals(zoomenEntity1.getSearchKey().trim())){
            sql.append("and a.`name` like '%"+zoomenEntity1.getSearchKey().trim()+"%'");
        }
        Connection conn=null;
        PreparedStatement st=null;
        ResultSet rs=null;
        //查出来的数据存在这里
        TableDTO returnDTO=new TableDTO();


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            rs = st.executeQuery();
            //存放连接数据库查出来的数据
            returnDTO.setData(fillData(rs));;


            return returnDTO;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            com.JdbcUtils_DBCP.release(conn,st,rs);
        }
        return null;
    }

    //从数据库查出来的数据
    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data=new Vector<>();
        while(rs.next()){
            Vector<Object> oneRecord =new Vector<>();

            String animalId= rs.getString("id");
            String name = rs.getString("name");
            String kind = rs.getString("kind");
            String introduction = rs.getString("introduction");
            String age = rs.getString("age");
            String health = rs.getString("health");
            String parkId = rs.getString("parkid");

            oneRecord.addElement(animalId);
            oneRecord.addElement(name);
            oneRecord.addElement(kind);
            oneRecord.addElement(introduction);
            oneRecord.addElement(age);
            oneRecord.addElement(health);
            oneRecord.addElement(parkId);


            data.addElement(oneRecord);
        }
        return data;
    }
    //添加功能
    @Override
    public boolean add(AnimalEntity animalEntity) {
        StringBuilder sql=new StringBuilder();
        sql.append("INSERT INTO `animals`(`name`,`kind`,`introduction`,`age`,`health`,`parkid`)" +
                " VALUES (?,?,?,?,?,?)");

        Connection conn=null;
        PreparedStatement st=null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());

            st.setString(1,animalEntity.getName());
            st.setString(2,animalEntity.getKind());
            st.setString(3,animalEntity.getIntroduction());
            st.setString(4,animalEntity.getAge());
            st.setString(5,animalEntity.getHealth());
            st.setString(6,animalEntity.getParkId());

            return st.executeUpdate()==1;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            com.JdbcUtils_DBCP.release(conn,st,null);
        }
        return false;
    }

    //修改功能
    @Override
    public boolean update(AnimalEntity animalEntity) {
        StringBuilder sql=new StringBuilder();
        sql.append("UPDATE `animals` SET `name` =?,`kind`=?,`introduction`=?, " +
                " `age`=?,`health`=?,`parkid`=? WHERE `id`=?");

        Connection conn=null;
        PreparedStatement st=null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());
            st.setString(1,animalEntity.getName());
            st.setString(2,animalEntity.getKind());
            st.setString(3,animalEntity.getIntroduction());
            st.setString(4,animalEntity.getAge());
            st.setString(5,animalEntity.getHealth());
            st.setString(6,animalEntity.getParkId());
            st.setInt(7,animalEntity.getAnimalId());



            return st.executeUpdate()==1;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            com.JdbcUtils_DBCP.release(conn,st,null);
        }
        return false;
    }

    @Override
    public boolean delete(AnimalEntity animalEntity) {
        StringBuilder sql=new StringBuilder();
        sql.append("delete from  `animals` WHERE `id`=?");

        Connection conn=null;
        PreparedStatement st=null;


        try {
            conn = com.JdbcUtils_DBCP.getConnection();
            st = conn.prepareStatement(sql.toString());

            st.setInt(1,animalEntity.getAnimalId());



            return st.executeUpdate()==1;


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            com.JdbcUtils_DBCP.release(conn,st,null);
        }



        return false;
    }
}
